Do SQL Plan Baselines Use Hints?
I posted this weekend on SQL Profiles and there were a couple of follow up comments that got me wondering about the internal mechanism used by Baselines and since this got a little wordy, I decided to make it a new post as opposed to just a comment. The documentation appears to indicate that Baselines actually store a plan, as opposed to the earlier mechanism used by both Outlines and SQL Profiles, which are hint based. But the DBA_SQL_PLAN_BASELINES view exposes a column called OTHER_XML which contains, you guessed it, hints. (by the way, this column also exists in the V$SQL_PLAN view, where it exposes hints for every plan in the shared pool) So anyway, the question was posed, “Do SQL Plan Baselines use hints?”. The answer is … I don’t think so. Here’s why:
- The DBA_SQL_PLAN_BASELINES view is based on SQLOBJ$, SQLOBJ$DATA, and SQLOBJ$AUXDATA. These tables contain data for both SQL Profiles and Baselines (but not for Outlines). The SQLOBJ$ table has a column (PLAN_ID) which is populated with a plan hash value (but not the one exposed in V$SQL – more on that later). And now the interesting bit, the entries for the Baselines have a value in the PLAN_ID column while the entries for the SQL Profiles don’t. Hmmmm…
- 10053 trace files have a section labeled “Final query after transformations:”. For statements using both Outlines and SQL Profiles, the “Final Query” includes hint text. For both fixed and non-fixed Baselines, the “Final Query” does not include hints.
So here’s some excerpts from three 10053 trace files. They were all created on the same 11.1.0.7 database. The first file was created for a statement using an Outline. The second was created for a statement using a SQL Profile. The third was created for a statement using a Baseline.
===================================================================================
10053 level 1 for statement using an Outline
===================================================================================
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=79200 hint_alias="SKEW"@"SEL$1"
SPM: statement not found in SMB
...
******************************************
----- Current SQL Statement for this session (sql_id=0r09xac72gvj1) -----
select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("SKEW") */ AVG("SKEW"."PK_COL") "AVG(PK_COL)" FROM "KSO"."SKEW" "SKEW" WHERE "SKEW"."COL1">0
...
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus@homer (TNS V1-V3) action=
sql_id=0r09xac72gvj1 plan_hash_value=568322376 problem_type=3
----- Current SQL Statement for this session (sql_id=0r09xac72gvj1) -----
select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
sql_text_length=60
sql=select /* test1 */ avg(pk_col) from kso.skew where col1 > 0
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 43K | |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS FULL | SKEW | 31M | 336M | 43K | 00:09:54 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("COL1">0)
Content of other_xml column
===========================
db_version : 11.1.0.7
parse_schema : SYS
plan_hash : 568322376
plan_hash_2 : 1429740808
outline : KSOTEST1
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = true
optimizer_features_hinted = 11.1.0.7
...
===================================================================================
10053 level 1 for statement using a manually generated SQL Profile
===================================================================================
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=79200 hint_alias="SKEW"@"SEL$1"
SPM: statement found in SMB
SPM: current statement has no SQL plan baseline, sig = 4814772129047009103
...
******************************************
----- Current SQL Statement for this session (sql_id=399m90n8jzpu6) -----
select avg(pk_col) from kso.skew
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX_FFS ("SKEW" "SYS_C0015494") */ AVG("SKEW"."PK_COL") "AVG(PK_COL)" FROM "KSO"."SKEW" "SKEW"
...
SPM: statement in SMB but no plan baseline, sig = 4814772129047009103
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus@homer (TNS V1-V3) action=
sql_id=399m90n8jzpu6 plan_hash_value=-2082116200 problem_type=3
----- Current SQL Statement for this session (sql_id=399m90n8jzpu6) -----
select avg(pk_col) from kso.skew
sql_text_length=33
sql=select avg(pk_col) from kso.skew
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 19K | |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN | SYS_C0015494| 31M | 183M | 19K | 00:04:58 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version : 11.1.0.7
parse_schema : SYS
plan_hash : 2212851096
plan_hash_2 : 4073867208
sql_profile : PROFILE_399m90n8jzpu6
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."PK_COL"))
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = true
optimizer_features_hinted = 11.1.0.7
...
===================================================================================
10053 level 1 for statement using a Baseline
===================================================================================
...
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=79200 hint_alias="SKEW"@"SEL$1"
SPM: statement found in SMB
DOP: Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=0qa98gcnnza7h) -----
select avg(pk_col) from kso.skew
where col1 > 0
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT AVG("SKEW"."PK_COL") "AVG(PK_COL)" FROM "KSO"."SKEW" "SKEW" WHERE "SKEW"."COL1">0
...
SPM: cost-based plan found in the plan baseline, planId = 1429740808
SPM: cost-based plan was successfully matched, planId = 1429740808
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus@homer (TNS V1-V3) action=
sql_id=0qa98gcnnza7h plan_hash_value=568322376 problem_type=3
----- Current SQL Statement for this session (sql_id=0qa98gcnnza7h) -----
select avg(pk_col) from kso.skew
where col1 > 0
sql_text_length=48
sql=select avg(pk_col) from kso.skew
where col1 > 0
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 43K | |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS FULL | SKEW | 31M | 336M | 43K | 00:09:54 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("COL1">0)
Content of other_xml column
===========================
db_version : 11.1.0.7
parse_schema : SYS
plan_hash : 568322376
plan_hash_2 : 1429740808
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SKEW"@"SEL$1")
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
...
A couple of final thoughts:
These are very simple examples, so they may not be representative of more complicated statements. I’ve had a quick look at some more realistic examples though, and the basic info I’ve presented seems to hold for them as well. It is clear however, that a ton of work is still going on when the statement is parsed. I’m assuming that is due to the optimizer’s attempts to see if a potentially better plan might exist, even with a fixed Baseline.
It appears that every statement is checked early in the parsing process to see if it is stored in the SMB (SQL Management Base) which is where Baselines are stored (and apparently where SQL Profiles are stored as well).
The 10053 trace shows a couple of plan hash values (PLAN_HASH and PLAN_HASH2). The one labeled PLAN_HASH is the one found in the V$SQL view, the one labeled PLAN_HASH2 is the one found in the SQLOBJ$ table. I’m not sure how to relate these two yet.
I’m hopeful that fixed Baselines will provide the ability to absolutely lock a Plan for a statement, as opposed to the hint based approaches. Not that those haven’t worked pretty well, but actually storing a plan instead of hints that try to limit the optimizers choices, if indeed that’s what’s actually happening, seems a much better approach.
That’s all for now. I’ve been working with Baselines in a production 11.1.0.7 database for a few weeks now and have been wanting to do a post on my observations but keep getting distracted. Hopefully in the next few days I will get a chance to do that.
Please let me know if you can shed any further light on Baselines. I’d be very interested to hear about any experiences with them.
On the other hand, I have not been able to find where the plan is actually stored for a Baseline. And I have seen the optimizer refuse to use a Baseline created on a hinted statement when attached to a non-hinted version of the same statement. In this case the 10053 trace said:
SPM: planId in plan baseline = 803197271, planId of reproduced plan = 2333352146
——- START SPM Plan Dump ——-
SPM: failed to reproduce the plan using the following info:
parse_schema name : SYS
plan_baseline signature : 1872586980585244352
plan_baseline plan_id : 803197271
plan_baseline hintset :
hint num 1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num 2 len 37 text: OPTIMIZER_FEATURES_ENABLE(‘11.1.0.7’)
hint num 3 len 22 text: DB_VERSION(‘11.1.0.7’)
hint num 4 len 8 text: ALL_ROWS
hint num 5 len 22 text: OUTLINE_LEAF(@”SEL$1″)
hint num 6 len 57 text: INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1” (“LITTLE_SKEW”.”COL1″))
SPM: generated non-matching plan:
hmmmm …
You said ” “Do SQL Plan Baselines use hints?”. The answer is … I don’t think so. ”
Christian Antognini said in his book on page 298
“Unfortunately, to display the list of hints associated with a SQL plan baseline, undocumented
data dictionary tables must be queried. The following SQL statement shows an example.
Note that since hints are stored in XML format, a conversion is necessary to have a readable
output.”
SQL> SELECT extractValue(value(h),’.’) AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h
4 WHERE so.name = ‘SYS_SQL_PLAN_8fb2691f3fdbb376’
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;
I am a bit confused Arent those listed in OUTLINE section are hints like FULL(@”SEL$1″ “SKEW”@”SEL$1”)
[…] I no longer think that Baselines store an actual plan for a SQL statement. (See my previous post – Do SQL Plan Baselines Use Hints?) I never could find a place where the plan data was stored (only the plan_hash_value). So I had a […]
Thanks for your comments. I just posted an update “Do Baselines Use Hints – Take 2” and I agree with you. It kept bugging me that I couldn’t find a place where the plan steps were actually stored. I could only find the plan hash value, so I did some more looking at 10053 trace files and it appears to me that the hints are actually used to attempt to match the plan hash value and if the optimizer can’t reproduce and exact match for some reason, it falls back to normal optimization. This is better than coming up with a different plan and not knowing it, as Outlines or SQL Profiles can do, but it is not quite what I was hoping for.
And yes, the outline section of XPLAN shows hints that the optimizer thinks are necessary to reproduce the plan (I think that’s what you were asking in the last sentence).
Kerry
[…] Osborne 1. Do SQL Plan Baselines use hints (pt.1) 2. Do SQL Plan Baselines use hints (pt. 2) 3. SQL Plan Management – SQL Plan Baselines 4. Why […]
[…] what’s in these SQLOBJ$ tables – no surprises it’s the baseline data. See also: Kerry Osborne – Do SQL Baselines Use Hints/ Christian Antognini – SQL Profiles in the Data Dictionary Christian Antognini – […]